USE [Test_Events_Temp]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spPrepareTables]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spPrepareTables]
GO

CREATE PROCEDURE [dbo].[spPrepareTables]
AS
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SyncLog') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[SyncLog](
			[Id] [int] IDENTITY(1,1) NOT NULL,
			[LastSyncDate] [datetime] NULL,
			[LastUpdate] [datetime] NULL,
			[ElapsedTime] [int] NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.CompanyProfiles') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[CompanyProfiles](
		[CompanyId] uniqueidentifier NOT NULL,
	    [CompanyName] nvarchar(max) NOT NULL,
		[CapitalIQID] nvarchar(max) NOT NULL,
		[LastUpdate] datetime NOT NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Alerts') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[Alerts](
		[Id] [uniqueidentifier] NOT NULL,
		[CompanyId] [uniqueidentifier] NOT NULL,
		[AlertDate] [datetime] NULL,
		[LevelOfImportance] [int] NOT NULL,
		[AlertType] [int] NOT NULL,
		[TypeOfChange] [int] NOT NULL,
		[Rationale] [nvarchar](750) NOT NULL,
		[ResourceDataSerialized] [nvarchar](max) NOT NULL,
		[Created] [datetime] NOT NULL,
		[LastUpdated] [datetime] NOT NULL,
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.EventIndicatorTemplates') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[EventIndicatorTemplates](
		[Id] [uniqueidentifier] NOT NULL,
		[Name] [nvarchar](max) NOT NULL,
		[Number] [nvarchar](max) NOT NULL,
		[SortKey] [int] NOT NULL,
		[ControversyIndicatorId] [uniqueidentifier] NOT NULL,
		[ControversyIndicatorName] [nvarchar](max) NOT NULL,
		[ControversyIndicatorNumber] [nvarchar](max) NOT NULL,
		[ControversyIndicatorSortKey] [int] NOT NULL,
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.PublishedEventIndicators') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[PublishedEventIndicators](
			[EventIndicatorId] [uniqueidentifier] NOT NULL,
			[EventIndicatorName] [nvarchar](450) NOT NULL,
			[EventIndicatorSortKey] [int] NOT NULL,
			[BaseEventIndicatorId] [uniqueidentifier] NOT NULL,
			[CompanyId] [uniqueidentifier] NOT NULL,
			[ControversyIndicatorId] [uniqueidentifier] NOT NULL,
			[ControversyIndicatorName] [nvarchar](450) NOT NULL,
			[ControversyIndicatorNumber] [nvarchar](20) NOT NULL,
			[ControversyIndicatorSortKey] [int] NOT NULL,
			[ProvidesControversy] [bit] NOT NULL,
			[ControversySummary] [nvarchar](1500) NULL,
			[AnswerCategoryId] [int] NOT NULL,
			[AnswerCategoryText] [nvarchar](450) NOT NULL,
			[AnswerCategoryScore] [int] NOT NULL,
			[AnswerCategoryJustification] [nvarchar](500) NOT NULL,
			[ImpactScore] [float] NOT NULL,
			[ImpactText] [nvarchar](300) NOT NULL,
			[ImpactCommentsId] [uniqueidentifier] NOT NULL,
			[RiskScore] [float] NOT NULL,
			[RiskText] [nvarchar](300) NOT NULL,
			[RiskCommentsId] [uniqueidentifier] NOT NULL,
			[PreparednessScore] [int] NOT NULL,
			[PreparednessText] [nvarchar](300) NOT NULL,
			[PreparednessCommentsId] [uniqueidentifier] NOT NULL,
			[OutlookScore] [int] NOT NULL,
			[OutlookText] [nvarchar](300) NOT NULL,
			[OutlookCommentsId] [uniqueidentifier] NOT NULL,
			[Timestamp] [datetime] NOT NULL,
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.PublishedEventIndicatorComments') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[PublishedEventIndicatorComments](
		    [CompanyId] [uniqueidentifier] NOT NULL,
			[EventIndicatorId] [uniqueidentifier] NOT NULL,
			[CommentId] [uniqueidentifier] NOT NULL,
			[CommentType] [int] NOT NULL,
			[Comment] [nvarchar](1200) NOT NULL,
			[CommentSortKey] [int] NOT NULL,
			[Timestamp] [datetime] NOT NULL,
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.PublishedIncidents') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[PublishedIncidents](
			[Id] [uniqueidentifier] NOT NULL,
			[CompanyId] [uniqueidentifier] NOT NULL,
			--[PeerGroupId] [uniqueidentifier] NOT NULL,
			--[ProfileStatus] [int] NULL,
			--[ThemeId] [uniqueidentifier] NULL,
			--[ThemeName] [nvarchar](50) NULL,
			--[ThemeSortKey] [int] NULL,
			[EventIndicatorId] [uniqueidentifier] NULL,
			[EventIndicatorName] [nvarchar](450) NULL,
			--[BaseEventIndicatorSortKey] [int] NULL,
			--[AnswerCategoryId] [int] NULL,
			[IncidentId] [nvarchar](50) NOT NULL,
			[Type] [int] NOT NULL,
			[Name] [nvarchar](450) NOT NULL,
			[Summary] [nvarchar](2500) NOT NULL,
			[Analysis] [nvarchar](2500) NOT NULL,
			--[MainTagId] [uniqueidentifier] NOT NULL,
			[MainTag] [nvarchar](450) NOT NULL,
			--[SubTag1] [nvarchar](450) NULL,
			--[SubTag2] [nvarchar](450) NULL,
			--[SubTag3] [nvarchar](450) NULL,
			--[SpecialTagId] [uniqueidentifier] NULL,
			--[SpecialTag] [nvarchar](100) NULL,
			[HasSupplyChainSpecialTag] [bit] NOT NULL,
			--[AdditionalSpecialTagId] [uniqueidentifier] NULL,
			--[AdditionalSpecialTag] [nvarchar](100) NULL,
			[Source] [nvarchar](450) NOT NULL,
			--[PublicationDate] [datetime] NOT NULL,
			--[CreationDate] [datetime] NOT NULL,
			[IncidentDate] [datetime] NOT NULL,
			[LocationAddress] [nvarchar](450) NOT NULL,
			[LocationLatitude] [float] NOT NULL,
			[LocationLongitude] [float] NOT NULL,
			[ImpactScore] [float] NOT NULL,
			[RiskScore] [float] NOT NULL,
			--[Injuries] [int] NOT NULL,
			--[Deaths] [int] NOT NULL,
			--[Fines] [float] NOT NULL,
			--[Settlement] [float] NOT NULL,
			--[AllegationStatus] [int] NOT NULL,
			--[ClaimSize] [float] NOT NULL,
			--[PollutionQuantity] [nvarchar](250) NOT NULL,
			--[PollutionDescription] [nvarchar](450) NOT NULL,
			--[Depth] [nvarchar](10) NULL,
			--[Breadth] [nvarchar](10) NULL,
			--[Duration] [nvarchar](10) NULL,
			--[Exceptionality] [nvarchar](10) NULL,
			--[Accountability] [nvarchar](40) NULL,
			--[CompanyAccountability] [nvarchar](10) NULL,
			--[Frequency] [nvarchar](10) NULL,
			--[ReachAndRelevance] [int] NULL,
			--[Tone] [nvarchar](20) NULL,
			--[ImportanceToStakeholders] [nvarchar](10) NULL,
			--[BusinessEthics] [nvarchar](10) NULL,
			--[Status] [int] NOT NULL,
			[LastUpdated] [datetime] NOT NULL,
			--[Reviewer] [nvarchar](100) NOT NULL,
			--[Analyst] [nvarchar](100) NOT NULL,
			--[IncidentsSummaryId] [uniqueidentifier] NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000')
			[Timestamp] [datetime] NOT NULL,
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.NewsArticles') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[NewsArticles](
		    [Id] [uniqueidentifier] NOT NULL,
		    [CompanyId] [uniqueidentifier] NOT NULL,
			[PublicationDate] [datetime] NOT NULL,
			[Headline] [nvarchar](max) NOT NULL,
			[ExternalUrl] [nvarchar](max) NOT NULL,
			[Source] [nvarchar](450) NOT NULL,
		) ON [PRIMARY]
	END
END
GO


